﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
//using System.Data.SqlServerCe;
using System.Data;
using System.Data.OleDb;
//using ErikEJ.Sql;



namespace PAPI_Import
{
    partial class Program
    {
        static void OperateWorkMode4()  //Invite PO Owners to update their data. Invites all when invoked regardless of date. Groups invites by owner email.
        {
            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            string sClearSQL = @"   SELECT email, max(first_name) AS First_Name, max(last_name) AS Last_Name, count(*) AS Amount
                                    FROM Papi_Users t1
                                    INNER JOIN Poes t2 ON t1.Requisitio=t2.Requisitioner
                                    WHERE t2.latest=1 AND t2.Status<99
                                    GROUP BY email";

            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
            SqlConn.Open();
            SqlDataReader dr = SqlCmd.ExecuteReader();

            int counter = 0;

            while (dr.Read())
            {
                counter++;

                String sFirstName= dr.GetString(dr.GetOrdinal("First_Name"));
                String sLastName= dr.GetString(dr.GetOrdinal("Last_Name"));
                String sEmail= dr.GetString(dr.GetOrdinal("email"));
                int iAmount = dr.GetInt32(dr.GetOrdinal("amount"));

                PapiImportSendMail(MailType.WarningMail, sEmail,
                    "Hello " + sFirstName + " " + sLastName + ", " + Environment.NewLine + Environment.NewLine +
                    "You have " + iAmount + " open item(s) in POMP system that require your attention. Please log in and handle it." 
                    + Environment.NewLine + Environment.NewLine + "Thank you," + Environment.NewLine + "POMP System",true);
            }

            dr.Close();
            SqlConn.Close();

            writeToLog("Reminder email(s) sent to " + counter + " user(s)");
            
            return;
        }
        

    }
}
